package dao;

import bean.GeneralBean;
import bean.UserBean;
import tools.UtilJDBC;

import java.sql.*;
import java.util.ArrayList;

/**
 * @Author 王志康
 * @Date 2021/9/6 14:09
 * @Version 1.0.0
 */
public class GeneralDao {

    /**
     * 添加商品
     *
     * @param general 添加的商品
     */
    public static void addItem(GeneralBean general) {
        Connection connection = UtilJDBC.connection;
        String sql = "insert into general " +
                "(product_id, product_name, product_area, product_price, " +
                "date_of_manufacture, quality_guarantee_period, on_the_shelf)" +
                "values(?,?,?,?,?,?,?)";
        try {

            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, general.getProductId());
            statement.setString(2, general.getProductName());
            statement.setString(3, general.getProductArea());
            statement.setDouble(4, general.getProductPrice());
            statement.setDate(5, new Date(general.getDateOfManufacture().getTime()));
            statement.setString(6,general.getQualityGuaranteePeriod());
            statement.setBoolean(7,general.isOnTheShelf());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除商品
     *
     * @param id 待删除的商品编号
     */
    public static void deleteGeneral(int id) {
        Connection connection = UtilJDBC.connection;
        String sql = "delete from general where product_id=?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取物品名称
     *
     * @param id 待获取名称的编号
     */
    public static void setOnTheShelfTrue(int id) {
        Connection connection = UtilJDBC.connection;
        String sql = "update general set on_the_shelf=1 where product_id=?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 物品下架
     *
     * @param id 待获取名称的编号
     */
    public static void setOnTheShelfFalse(int id) {
        Connection connection = UtilJDBC.connection;
        String sql = "update general set on_the_shelf=0 where product_id=?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 检查是否存在该物品
     *
     * @param id 待检查的编号
     * @return 是否存在此编号对应的物品
     */
    public static boolean verification(String id) {
        Connection connection = UtilJDBC.connection;
        String sql = "select product_id from general where product_id=?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, id);
            ResultSet resultSet = statement.executeQuery();
            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 获取物品价格
     *
     * @param id 待获取价格的编号
     * @return 此物品的价格
     */
    public static double getPrice(String id) {
        Connection connection = UtilJDBC.connection;
        String sql = "select product_price from general where product_id=?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, id);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getDouble("product_price");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 获取物品名称
     *
     * @param id 待获取名称的编号
     * @return 此物品的名称
     */
    public static String getName(String id) {
        Connection connection = UtilJDBC.connection;
        String sql = "select product_name from general where product_id=?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, id);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getString("product_name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 获取所有水果信息
     *
     * @return 包含所有水果信息的arraylist
     */
    public static ArrayList<GeneralBean> getAllGeneralList() {
        Connection connection = UtilJDBC.connection;
        ArrayList<GeneralBean> list = new ArrayList<>();
        String sql = "select product_id, product_name, product_area," +
                " product_price, date_of_manufacture, " +
                "quality_guarantee_period, on_the_shelf from general";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new GeneralBean(
                        resultSet.getInt("product_id"),
                        resultSet.getString("product_name"),
                        resultSet.getDouble("product_price"),
                        resultSet.getInt("on_the_shelf") == 1,
                        resultSet.getDate("date_of_manufacture"),
                        resultSet.getString("quality_guarantee_period"),
                        resultSet.getString("product_area")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取上架水果信息
     *
     * @return 包含所有水果信息的arraylist
     */
    public static ArrayList<GeneralBean> getGeneralList() {
        Connection connection = UtilJDBC.connection;
        ArrayList<GeneralBean> list = new ArrayList<>();
        String sql = "select product_id, product_name, product_area," +
                " product_price, date_of_manufacture, " +
                "quality_guarantee_period, on_the_shelf " +
                "from general where on_the_shelf=true" ;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new GeneralBean(
                        resultSet.getInt("product_id"),
                        resultSet.getString("product_name"),
                        resultSet.getDouble("product_price"),
                        resultSet.getInt("on_the_shelf") == 1,
                        resultSet.getDate("date_of_manufacture"),
                        resultSet.getString("quality_guarantee_period"),
                        resultSet.getString("product_area")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
